package com.nextStep.jdbcOper;

import com.nextStep.entity.DbConfig;
import com.nextStep.utils.StringUtils;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository("dbExecuteDao")
public class DbExecuteDaoImpl implements DbExecuteDao {
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }




    @Override
    public List showTables(DbConfig dbConfig) throws SQLException {
        final String order = "show tables";
        Connection conn = DriverManager.getConnection(dbConfig.getDburl(),dbConfig.getDbusername(),dbConfig.getDbpassword());
        List list = new ArrayList();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(order);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(rs.getString(1));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            if(conn!=null){
                conn.close();
            }
            if(rs!=null){
                rs.close();
            }
            if(ps!=null) {
                ps.close();
            }
        }
        return list;
    }

    @Override
    public List selectTable(DbConfig dbConfig,String tableName,Integer pageNum,Integer pageSize) throws SQLException {
        final String orderColumns="SHOW FULL COLUMNS FROM "+tableName;
        List list = new ArrayList();
        Connection conn = DriverManager.getConnection(dbConfig.getDburl(),dbConfig.getDbusername(),dbConfig.getDbpassword());
        ResultSet rs = null;
        PreparedStatement ps = null;
        ResultSetMetaData rsmd = null;
        try {
            ps = conn.prepareStatement(orderColumns);
            rs = ps.executeQuery();


            List listRow = new ArrayList();
            while (rs.next()) {
                listRow.add(rs.getString(1));
            }
            list.add(listRow);

            final String orderTable = "select * from " + tableName +" order by "+((List)(list.get(0))).get(0)+" limit "+pageNum*pageSize+","+pageSize;
            System.out.println("sql:"+orderTable);

            ps = conn.prepareStatement(orderTable);
            rs = ps.executeQuery();
            rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();  //获取总列数量
            while (rs.next()) {
                List listRows = new ArrayList();
                for (int i = 1; i < columnCount+1; i++) { //获取当前列
                    listRows.add(rs.getObject(i));
                }
                list.add(listRows);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            if(conn!=null){
                conn.close();
            }
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
        return list;

    }

    @Override
    public List selectColumns(DbConfig dbConfig,String tableName) throws SQLException {
        final String order="SHOW FULL COLUMNS FROM "+tableName;
        Connection conn = DriverManager.getConnection(dbConfig.getDburl(),dbConfig.getDbusername(),dbConfig.getDbpassword());
        List list = new ArrayList();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(order);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(rs.getString(1));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(conn!=null){
                conn.close();
            }
            if(rs!=null){
                rs.close();
            }
            if(ps!=null) {
                ps.close();
            }
        }
        return list;
    }

    @Override
    public Integer selectLimitSumNum(DbConfig dbConfig, String tableName, Integer pageSize) throws SQLException {
        final String orderColumns="select count(*) from " + tableName;
        Integer pageLimitSunNum = 0;
        Connection conn = DriverManager.getConnection(dbConfig.getDburl(),dbConfig.getDbusername(),dbConfig.getDbpassword());
        ResultSet rs = null;
        PreparedStatement ps = null;
        ResultSetMetaData rsmd = null;
        try {
            ps = conn.prepareStatement(orderColumns);
            rs = ps.executeQuery();

            if (rs.next()) {
                pageLimitSunNum= Integer.valueOf(rs.getString(1));
            }
            System.out.println("sql:"+orderColumns);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            if(conn!=null){
                conn.close();
            }
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
        int retuPage= pageLimitSunNum/pageSize;
        if( (pageLimitSunNum/(pageSize*1.0))%1>0) {
            retuPage+=1;
        }
        return retuPage;
    }

    @Override
    public List condTable(DbConfig dbConfig, String tableName, String selectColumns, String dataSelectInput) throws SQLException {
        final String orderColumns="SHOW FULL COLUMNS FROM "+tableName;
        final String orderType ="desc  "+tableName;
        List list = new ArrayList();
        Connection conn = DriverManager.getConnection(dbConfig.getDburl(),dbConfig.getDbusername(),dbConfig.getDbpassword());
        ResultSet rs = null;
        PreparedStatement ps = null;
        ResultSetMetaData rsmd = null;
        Boolean columnCharOrNum = false;   //条件字段是否需要加引号
        try {
            //获取列名
            ps = conn.prepareStatement(orderColumns);
            rs = ps.executeQuery();
            List listRow = new ArrayList();
            while (rs.next()) {
                listRow.add(rs.getString(1));
            }
            list.add(listRow);


            //获取条件字段类型，来确定要不要加引号
            ps = conn.prepareStatement(orderType);
            rs = ps.executeQuery();
            while (rs.next()) {
                if(rs.getString(1).equals(selectColumns)){   //等于我要检索的字段
                    String columnType = rs.getString(2);    //获取检索字段类型
                    final String[] charType = new String[]{"varchar","char","nvarchar","nchar","text","ntext","datetime","date","year","time","timestamp"};
                    for (int i = 0; i < charType.length; i++) {
                        if(columnType.indexOf(charType[i])!=-1){
                            columnCharOrNum = true;
                            break;
                        }
                    }
                    break;
                }
            }

            String selectColumnsValue = null;
            if(columnCharOrNum)     {
                selectColumnsValue = "\'"+dataSelectInput+"\'";
            }else{
                selectColumnsValue = dataSelectInput;
            }
            String orderTableAdd=" where "+selectColumns +" = "+selectColumnsValue;
            if("".equals(dataSelectInput)){      //如果条件为空，则查询全部
                orderTableAdd="";
            }
            final String orderTable = "select * from " + tableName +orderTableAdd+" order by "+((List)(list.get(0))).get(0);
            ps = conn.prepareStatement(orderTable);
            rs = ps.executeQuery();
            rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();  //获取总列数量
            while (rs.next()) {
                List listRowa = new ArrayList();
                for (int i = 1; i < columnCount+1; i++) { //获取当前列
                    listRowa.add(rs.getObject(i));
                }
                list.add(listRowa);
            }
            System.out.println("sql:"+orderTable);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            if(conn!=null){
                conn.close();
            }
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
        return list;
    }

    @Override
    public int deleteData(DbConfig dbConfig, String tableName, String idValue) throws SQLException {
        final String orderColumnId="SHOW FULL COLUMNS FROM "+tableName;
        String idWhere = "";    //删除条件字段名
        Connection conn = DriverManager.getConnection(dbConfig.getDburl(),dbConfig.getDbusername(),dbConfig.getDbpassword());
        conn.setAutoCommit(false);   //手动提交事务
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            //获取列名
            ps = conn.prepareStatement(orderColumnId);
            rs = ps.executeQuery();
            final String[] charType = new String[]{"varchar","char","nvarchar","nchar","text","ntext","datetime","date","year","time","timestamp"};
            boolean yhFlag = false;
            if (rs.next()) {
                idWhere = (rs.getString(1));
                String columnId = rs.getString(2);
                int i ;
                for ( i = 0; i < charType.length; i++) {
                    if(columnId.indexOf(charType[i])!=-1){ //需要加引号
                        yhFlag = true;
                        break;
                    }
                }

                String columnType = rs.getString(2);    //获取检索字段类型
            }
            if(yhFlag){
                idValue="\'"+idValue+"\'";
            }
            final String orderDelete="delete from " + tableName+" where "+idWhere+" = "+idValue;

            ps = conn.prepareStatement(orderDelete);
            int status = ps.executeUpdate();
            System.out.println("sql:"+orderDelete);
            if(status != 1){
                conn.rollback();
                return 0;
            }
            else{
                conn.commit();
                return 1;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(conn!=null){
                conn.close();
            }
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
        return 0;
    }

    @Override
    public List<List> updateRow(DbConfig dbConfig, String tbName, String rowIdValue) throws SQLException {
        final String orderColumnId="SHOW FULL COLUMNS FROM "+tbName;
        Connection conn = DriverManager.getConnection(dbConfig.getDburl(),dbConfig.getDbusername(),dbConfig.getDbpassword());
        PreparedStatement ps = null;
        ResultSet rs = null;
        ResultSetMetaData rsmd = null;
        List<List> list = new ArrayList<>();

        try {

            //获取列名
            ps = conn.prepareStatement(orderColumnId);
            rs = ps.executeQuery();
            List<String> listColumn = new ArrayList<>();
            while (rs.next()) {
                listColumn.add(rs.getString(1));
            }
            final String orderSelect="select * from " + tbName+" where "+listColumn.get(0)+" = "+rowIdValue;
            System.out.println("sql:"+orderSelect);
            ps = conn.prepareStatement(orderSelect);
            rs = ps.executeQuery();
            rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();  //获取总列数量
            List<String> listValue = new ArrayList<>();
            if (rs.next()) {
                for (int i = 1; i <columnCount+1 ; i++) {
                    String strValue = rs.getString(i)==null?"":rs.getString(i);
                    listValue.add(strValue);
                }
            }

            list.add(listColumn);
            list.add(listValue);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(conn!=null){
                conn.close();
            }
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
        return list;
    }

    @Override
    public int updateRowData(DbConfig dbConfig, String tbName, String[] arrId, String[] arrValue) throws SQLException {
        final String orderType ="desc  "+tbName;

        Connection conn = DriverManager.getConnection(dbConfig.getDburl(),dbConfig.getDbusername(),dbConfig.getDbpassword());
        conn.setAutoCommit(false);   //手动提交事务
        ResultSet rs = null;
        PreparedStatement ps = null;
        ResultSetMetaData rsmd = null;
        Boolean[] yhFlag=new Boolean[arrId.length];   //当前字段是否加引号，true加，false不加
        int status=0;  //返回受影响行数

        try {

            //获取条件字段类型，来确定要不要加引号
            ps = conn.prepareStatement(orderType);
            rs = ps.executeQuery();
            int yhI= 0 ;
            final String[] charType = new String[]{"varchar","char","nvarchar","nchar","text","ntext","datetime","date","year","time","timestamp"};
            while (rs.next()) {
                String columnType = rs.getString(2);    //获取检索字段类型
                int i ;
                for ( i = 0; i < charType.length; i++) {
                    if(columnType.indexOf(charType[i])!=-1){ //需要加引号
                        yhFlag[yhI] = true;
                        break;
                    }
                }
                if(i==charType.length){
                    yhFlag[yhI] = false;
                }
                yhI++;

            }

            String orderColumnId="update "+tbName +" set ";
            for (int i = 0; i < arrId.length; i++) {
                if(yhFlag[i]){ //需要
                    orderColumnId += arrId[i] +"=\'"+arrValue[i]+"\',";
                }else {  //不需要
                    if(arrValue[i] ==""||arrValue[i]==null){
                        arrValue[i]="0";
                    }
                    orderColumnId += arrId[i] +"="+arrValue[i]+",";
                }

            }
            orderColumnId = orderColumnId.substring(0,orderColumnId.length()-1) ;   //截取最后一个逗号
            orderColumnId+=" where ";
            if(yhFlag[0]){ //需要
                orderColumnId += arrId[0] +"=\'"+arrValue[0]+"\'";
            }else {  //不需要
                if(arrValue[0] ==""||arrValue[0]==null){
                    arrValue[0]="0";
                }
                orderColumnId += arrId[0] +"="+arrValue[0];
            }
            System.out.println("sql:"+orderColumnId);

            ps = conn.prepareStatement(orderColumnId);
            status = ps.executeUpdate();

            if(status != 1){
                conn.rollback();
                return 0;
            }else{
                conn.commit();
                return 1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(conn!=null){
                conn.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
        return status;
    }

    @Override
    public Integer addRow(DbConfig dbConfig, String tbName, String[] arrId, String[] arrValue) throws SQLException {
        final String orderType ="desc  "+tbName;

        Connection conn = DriverManager.getConnection(dbConfig.getDburl(),dbConfig.getDbusername(),dbConfig.getDbpassword());
        conn.setAutoCommit(false);
        ResultSet rs = null;
        PreparedStatement ps = null;
        ResultSetMetaData rsmd = null;
        Boolean[] yhFlag=new Boolean[arrId.length];   //当前字段是否加引号，true加，false不加
        Integer status=0;  //返回受影响行数

        try {

            //获取条件字段类型，来确定要不要加引号
            ps = conn.prepareStatement(orderType);
            rs = ps.executeQuery();
            int yhI= 0 ;
            final String[] charType = new String[]{"varchar","char","nvarchar","nchar","text","ntext","datetime","date","year","time","timestamp"};
            while (rs.next()) {
                String columnType = rs.getString(2);    //获取检索字段类型
                int i ;
                for ( i = 0; i < charType.length; i++) {
                    if(columnType.indexOf(charType[i])!=-1){ //需要加引号
                        yhFlag[yhI] = true;
                        break;
                    }
                }
                if(i==charType.length){
                    yhFlag[yhI] = false;
                }
                yhI++;
            }

            String orderColumn="insert into  "+tbName +" ( ";
            String orderValue=" values(";
            for (int i = 0; i < arrId.length; i++) {
                if( arrValue[i] == null || arrValue[i].equals("")){  //跳过该字段
                    continue;
                }
                orderColumn+=arrId[i]+",";
                if(yhFlag[i]){ //需要
                    orderValue += "\'"+arrValue[i]+"\',";
                }else {  //不需要
                    if(arrValue[i] ==""||arrValue[i]==null){
                        arrValue[i]="0";
                    }
                    orderValue += arrValue[i]+",";
                }

            }
            orderColumn = orderColumn.substring(0,orderColumn.length()-1) ;   //截取最后一个逗号
            orderValue = orderValue.substring(0,orderValue.length()-1) ;   //截取最后一个逗号
            orderColumn+=") ";
            orderValue+=") ";

            System.out.println("sql:"+orderColumn+orderValue);

            ps = conn.prepareStatement(orderColumn+orderValue);
            status = ps.executeUpdate();
            if(status != 1){
                conn.rollback();
                return 0;
            }else{
                conn.commit();
                return 1;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(conn!=null){
                conn.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
        return status;

    }

    @Override
    public boolean dbconfigTest(DbConfig dbConfig) {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(dbConfig.getDburl(), dbConfig.getDbusername(), dbConfig.getDbpassword());
            conn.close();
            return true;
        } catch (SQLException e) {
            return false;
        }
    }

    @Override
    public List<List> runSql(DbConfig dbConfig, String sql) throws SQLException {
        Connection conn =null;
        Statement st = null;
        ResultSet rs = null;
        ResultSetMetaData rsmd = null;
        List<List> dataList = new ArrayList<>();

        try {
            conn = DriverManager.getConnection(dbConfig.getDburl(),dbConfig.getDbusername(),dbConfig.getDbpassword());
            st = conn.createStatement();
            boolean hasResultSet = st.execute(sql);
            if(hasResultSet){

                rs=st.getResultSet();
                rsmd = rs.getMetaData();
                int columnCount = rsmd.getColumnCount();  //获取总列数量

                sql = sql.toLowerCase();
                //查询列名
                if( sql.trim().indexOf("select ")==0 ){  //是select 命令
                    sql = sql.substring(sql.indexOf("from ")+5 );
                    String[] tableName = sql.split(" ");
                    List columns = selectColumns(dbConfig,tableName[0]);
                    dataList.add(columns);
                }else{
                    List columns = new ArrayList();
                    for (int i = 0; i < columnCount; i++) {
                        columns.add("列"+(i+1));
                    }
                    dataList.add(columns);

                }
                //


                while(rs.next()){
                    List<String> rowList = new ArrayList<>();
                    for (int i = 1; i < columnCount+1; i++) {
                        String addValue = StringUtils.isNull(rs.getString(i) )?"":rs.getString(i);
                        rowList.add( addValue  );

                    }
                    dataList.add(rowList);
                }
            }else{
                List titleList = new ArrayList();
                titleList.add("受影响行数");
                List valueList = new ArrayList();
                valueList.add( st.getUpdateCount() );
                dataList.add( titleList );
                dataList.add( valueList );
            }

        }catch (SQLException e){
            List titleList = new ArrayList();
            titleList.add("错误");
            List valueList = new ArrayList();
            valueList.add( e.toString() );
            dataList.add( titleList );
            dataList.add( valueList );
        }
        finally
        {
            if(conn!=null){
                conn.close();
            }
            if (st != null) {
                st.close();
            }
            if(rs!=null){
                rs.close();
            }

        }
        return dataList;

    }

    @Override
    public void test() {
        System.out.println("dbExecute dao test");
    }
}

